<?php
declare (strict_types=1);

namespace app\Api\controller;

use Ali\OSS\OSS;
use PHPExcel_IOFactory;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\db\exception\DbException;
use think\Exception;
use think\facade\Config;
use think\facade\Db;
use think\Request;
use think\Response;
use app\validate\SignUp as SignUpValidate;
use think\response\Json;

class SignUp
{
    const TABLE_NAME = "active_user_signup_";
    protected SignUpValidate $validate;

    public function __construct()
    {
        $this->validate = new SignUpValidate();
    }

    /**
     * 显示资源列表
     *
     * @param Request $request
     * @return Response
     */
    public function index(Request $request): Response
    {
        $params = $request->get();
        // 校验是否传了活动id 必填项
        if (!$this->validate->scene('search')->check($params)) {
            return error([], 9001, $this->validate->getError());
        }
        $active_id = $params['active_id'];
        $config = Config::get('database');
        $page = (int)$params['pageNum'];
        $pageSize = (int)$params['pageSize'];
        $cache = $request->cache;

        // 获取表前缀
        $tablePrefix = $config['connections'][$config['default']]['prefix'];
        try {
            //判断指定的活动报名表是否存在
            if (!checkTableExists($tablePrefix . self::TABLE_NAME . $active_id)) {
                // 指定活动的报名表不存在则根据之前的表进行复制
                if (!copyTable($tablePrefix . self::TABLE_NAME, $tablePrefix . self::TABLE_NAME . $active_id, '活动id为' . $active_id . '的活动报名表')) {
                    throw new Exception('创建活动报名表失败');
                }
            }
            $query = Db::name(self::TABLE_NAME . $params['active_id'])->where('isdelete', '=', 0);

            // 拼接查询条件
            if (isset($params['keyword'])) {
                $query->where('student.name', 'like', "%{$params['keyword']}%");
            }
            if (isset($params['phone'])) {
                $query->where('student.phone', '=', $params['phone']);
            }
            if (isset($params['active_id'])) {
                $query->where('active_group.active_id', '=', $params['active_id']);
            }
            if (isset($params['title'])) {
                $query->where('active_group.title', 'like', "%{$params['title']}%");
            }
            if (isset($params['group'])) {
                $query->where('active_group.group_tag', '=', $params['group']);
            }
            if (isset($params['type'])) {
                if ($params['type'] == 1) {
                    $query->where('active_group_tag.type', '=',2);
                } else {
                    $query->where('active_group_tag.type', '=',3);
                }
            }
            $field = "student.province,student.city,active.id as active_id,active_group.id,GROUP_CONCAT(student.studentname SEPARATOR '/') as studentname,active.name as active_name,title,active_group_tag.name as group_tag_name,active_group_tag.tag as group_tag,student.postinfo as postinfo,organ.name as ins_name,submit_status,pay_status";
            $paginate = $query->alias('signup')
                ->join('active_group_' . $params['active_id'] . ' active_group', 'signup.group_id=active_group.id')
                ->join('organ', 'signup.institution_id=organ.id')
                ->join('active_user_baseinfo student', 'signup.baseinfo_id=student.id')
                ->join('active', 'active_group.active_id=active.id')
                ->join('active_group_tag', 'active_group.group_tag=active_group_tag.id')
                ->group('active_group.id')
                ->field($field)->paginate(['page' => $page, 'list_rows' => $pageSize]);
            return success([
                'total' => $paginate->total(),
                'list' => $paginate->items(),
                'current_page' => $paginate->currentPage(),
                'last_page' => $paginate->lastPage(),
                'debug' => Db::getLastSql()
            ]);
        } catch (\Exception $e) {
            writeLogToDb('查询报名信息失败', $cache['id'], 1, Db::getLastSql() . '///' . $e->getMessage() . '/活动id:' . $active_id);
            return error([1], 9006, $e->getMessage());
        }

    }

    /**
     * 显示创建资源表单页.
     *
     * @return Response
     */
    public function create()
    {
        //
    }

    /**
     * 保存新建的资源
     *
     * @param Request $request
     * @return Response
     */
    public function save(Request $request): Response
    {
        //
        $params = $request->post();
        if (!$this->validate->scene('insert')->check($params)) {
            return error([], 9004, $this->validate->getError());
        }
        $cahce = $request->cache;
        $teacherInfo = [];
        try {
            // 查询所有学生信息
            $studentList = Db::name('active_user_baseinfo')->where('id', 'in', $params['student_id'])->select()->toArray();
            // 判断是否选择了指导教师
            if (isset($params['teacher_id']) && !empty($params['teacher_id'])) {
                $teacherInfo = Db::name('teacher_baseinfo')->where('id', '=', $params['teacher_id'])->find();
            }
            // 开启事务
            Db::startTrans();
            $time = time();
            // 先创建分组表数据
            $groupInsertData = array(
                'active_id' => $params['active_id'],
                'group_tag' => $params['group_tag'],
                'title' => $params['title'],
                'media_type' => $params['media_type'],
                'institution_id' => $params['ins_id'],
                'create_time' => $time,
                'baseinfo_id' => implode('-', $params['student_id']),
                'media_url' => $params['media_type'] != 0 ? $params['media_url'][0] : '',
                'submit_status' => 1
            );
            $groupInsertResult = Db::name('active_group_' . $params['active_id'])->insertGetId($groupInsertData);
            if ($groupInsertResult) {
                $signUpInsertData = [];
                foreach ($studentList as $student) {
                    $signUpInsertData[] = array(
                        'guide_teacher' => $params['teacher_id'],
                        'teacher_phone' => $params['teacher_id'] ? $teacherInfo['phone'] : '',
                        'group_id' => $groupInsertResult,
                        'baseinfo_id' => $student['id'],
                        'institution_id' => $params['ins_id'],
                        'ishz' => $params['ishz'],
                        'isps' => $params['isps'],
                        'issp' => $params['issp'],
                        'create_time' => $time
                    );
                }
                $signUpInsertResult = Db::name(self::TABLE_NAME . $params['active_id'])->insertAll($signUpInsertData);
                if ($signUpInsertResult) {
                    Db::commit();
                    writeLogToDb('报名成功', $cahce['id'], 1, ['group_id' => $groupInsertResult]);
                    return success([], 0, '报名成功');
                } else {
                    Db::rollback();
                    // 抛出异常
                    throw new \think\Exception('报名表创建失败');
                }

            } else {
                throw new Exception('创建组合信息失败');
            }

        } catch (\Exception $e) {
            // 记录日志
            writeLogToDb('新增报名失败', $cahce['id'], 1, ['message' => $e->getMessage(), 'params' => $params]);
            return error([], 9006, $e->getMessage());
        }
    }

    /**
     * 显示指定的资源
     *
     * @param int $id
     * @return Response
     */
    public function read(int $id, Request $request)
    {
        $params = $request->get();
        try {
            //先查询出分组信息
            $groupData = Db::name('active_group_' . $params['active_id'])->field('id,active_id,group_tag,title,media_type,media_url,institution_id as ins_id,baseinfo_id as student_id')->find($id);
            $signUpData = Db::name(self::TABLE_NAME . $groupData['active_id'])->alias('signup')->join('active_user_baseinfo baseinfo', 'signup.baseinfo_id=baseinfo.id')->where('group_id', '=', $id)->select();

            $groupData['signUp'] = $signUpData;
            $groupData['teacher_id'] = $signUpData[0]['guide_teacher'];
            return success($groupData);
        } catch (\Exception $e) {
            return error([], 9006, '非法操作' . $e->getMessage());
        }

    }

    /**
     * 显示编辑资源表单页.
     *
     * @param int $id
     * @return Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * 保存更新的资源
     *
     * @param Request $request
     * @param int $id
     * @return Response
     */
    public function update(int $id, Request $request,)
    {
        //
        $params = $request->post();
        if (!$this->validate->scene('insert')->check($params)) {
            return error([], 9904, $this->validate->getError());
        }
        $cache = $request->cache;
        // 开启事务
        Db::startTrans();
        try {
            //  查询旧数据信息
            $oldData = Db::name('active_group_' . $params['active_id'])->find($id);

            $updateData = array(
                'id' => $params['id'],
                'active_id' => $params['active_id'],
                'group_tag' => $params['group_tag'],
                'title' => $params['title'],
                'media_type' => $params['media_type'],
                'media_url' => $params['media_type'] != 0 ? $params['media_url'][0] : '',
                'submit_status' => 1
            );
            // 执行分组表修改操作
            $updateResult = Db::name('active_group')->save($updateData);

            if ($updateResult !== false) {
                // 校对素材地址是否一致
                if ($oldData['media_url'] != $updateData['media_url']) {
                    $oss = new OSS();
                    if ($oss->deleteFile($oldData['media_url'])) {
                        Db::commit();
                        writeLogToDb('修改报名信息成功', $cache['id'], 1, ['params' => $params]);
                        return success([], 0, '修改报名信息成功');
                    } else {
                        throw new Exception('背景素材山删除失败');
                    }
                } else {
                    Db::commit();
                    writeLogToDb('修改报名信息成功', $cache['id'], 1, ['params' => $params]);
                    return success([], 0, '修改报名信息成功');
                }
            } else {
                throw new Exception('修改报名信息失败');
            }
        } catch (\Exception $e) {
            Db::rollback();
            writeLogToDb('修改报名信息失败', $cache['id'], 1, ['message' => $e->getMessage(), 'params' => $params]);
            return error([], '9006', $e->getMessage());
        }


    }

    /**
     * 删除指定资源
     *
     * @param Request $request
     * @return Response
     * @throws Exception
     */
    public function delete(Request $request): Response
    {

        try {
            // 校验该条数据是否在数据库里存在
            $active_id = $request->post('active_id');
            $ids = $request->post('ids');
            $cache = $request->cache;
            $check = Db::name('active_group_' . $active_id)->where('id', 'in', $ids)->count();

            if ($check > 0) {
                $deleteResult = Db::name('active_group_' . $active_id)->where('id', 'in', $ids)->update(['isdelete' => 1]);
                if ($deleteResult !== false) {
                    writeLogToDb('删除报名成功', $cache['id'], 1, ['params' => $ids]);
                    return success([], 0, '报名删除成功');
                } else {
                    throw new Exception('报名删除失败',9005);
                }
            } else {
                throw new Exception('非法操作',9004);
            }
        } catch (DbException $e) {
            writeLogToDb('删除报名失败', $cache['id'], 1, ['message' => $e->getMessage(), 'params' => $ids]);
            return error([], $e->getCode()?:9006, $e->getMessage());
        }
    }

    /**
     * @return void
     */
    public function pay(int $id, Request $request)
    {
        $cache = $request->cache;
        $params = $request->post();
        try {
            $check = Db::name('active_group_' . $params['active_id'])->find($id);
            if ($check != null) {
                $data = [
                    'pay_status' => $check['pay_status'] == 0 ? 1 : 0
                ];
                $update = Db::name('active_group_' . $params['active_id'])->where('id', '=', $id)->update($data);
                if ($update !== false) {
                    writeLogToDb('修改缴费状态成功', $cache['id'], 1, ['params' => $params, 'message' => $check['pay_status'] == 0 ? '改为缴费状态' : '改为未缴费状态']);
                    return success();
                } else {
                    throw new Exception('修改缴费状态失败');
                }
            } else {
                throw new Exception('非法操作,该报名信息不存在');
            }
        } catch (\Exception $e) {
            writeLogToDb('修改缴费状态失败', $cache['id'], 1, ['message' => $e->getMessage()]);
            return error([], 9006, $e->getMessage());
        }
    }

    public function downloadTemplate(int $id)
    {
        // 查询活动配置是否已经设置
        $config = Db::name('active_config')->where('active_id','=',$id)->find();
        if(empty($config)){
            return  error([],9005,'当前活动尚未开始，请联系平台管理员');
        }
        $spreadsheet = IOFactory::load(root_path().'/public/excel_files/baomingTemplate.xlsx');
        $sheet = $spreadsheet->getActiveSheet();
        $filename = root_path() . '/public/excel_files/baomingTemplate.xlsx';
        return Response::create($filename, 'file', 200)
            ->contentType('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->header(['Content-Disposition' => 'attachment; filename="baomingTemplate.xlsx"']);
    }

    /**
     * 导入报名信息
     * @param Request $request
     * @return Response
     */
    public function importSignUp(Request $request)
    {
        ini_set('memory_limit', '512M');
        $cache = $request->cache;
        $file = request()->file('file');
        $params = $request->get();
        $allowedExtensions = ['xls', 'xlsx'];
        $filename = $_FILES['file']['name'];
        $extension = pathinfo($filename, PATHINFO_EXTENSION);
        try {
            if (empty($params['activeId'])) {
                throw new Exception('请选择活动', 9002);
            }
            if (!in_array(strtolower($extension), $allowedExtensions)) {
                throw new Exception('文件格式错误', 9002);
            }
            // 校验是否存在配置
            $filePath = $file->getRealPath();
            // 把表单里面的数据存储入data中
            $data = readExcelToArray($filePath);
            $header = $data[1];
            // 移除表单中无用的前几行数据
            array_splice($data, 0, 6);

            // 根据预设header名来获取对应阻断在data中的行级下标
            $insIndex = 13;
            $wanttimeIndex = array_search('预约参展日期', $header);
            $wanttimeslotIndex = array_search('预约时段', $header);
            $remarkIndex = array_search('备注', $header);
            $teacherIndex = 16;
            $titleIndex = array_search('作品名称', $header);
            $isspIndex = array_search('是否需要\n视频U盘', $header);
            $ishzIndex = array_search('是否需要\n化妆服务', $header);
            $ispsIndex = array_search('是否需要\n定制相册', $header);
            //把所有机构名称都取出来
            $insNames = array_filter(array_unique(array_column($data, $insIndex)));
            // 查询机构信息
            $institution = Db::name('organ')->where('name', 'in', $insNames)->select()->toArray();
//            return  Db::getLastSql();
            if (count($insNames) != count($institution)) {
                throw new Exception('存在错误的机构名称，请仔细检查', 9009);
            }
            // 把机构名称作为key
            $insOptions = convertValueToKey($institution, 'name');
            // 取出机构的id作为数组
            $insIds = array_column($institution, 'id');

            //  查询机构中所有教师的信息
            $insTeacher = Db::name('teacher_baseinfo')->where('institution_id', 'in', $insIds)->select()->toArray();
            // 以教师的手机号作为key
            $teacherOptions = convertValueToKey($insTeacher, 'phone');
            //把所有老师的手机号取出来作为新数组
            $teacherMobiles = array_column($insTeacher, 'phone');
            // 查询机构中所有学生的信息
            $insStudentData = Db::name('active_user_baseinfo')->where('institution_id', 'in', $insIds)->select()->toArray();
            //把学生的身份证号取出来作为新数组元素
            $id_cards = array_column($insStudentData, 'id_card');
            // 以学生的身份证号作为key
            $studentOptions = convertValueToKey($insStudentData, 'id_card');
            // 查询所有group_tag信息
            $tagData = Db::name('active_group_tag')->select()->toArray();
            // 以分组的名称作为key
            $tagOptions = convertValueToKey($tagData, 'name');
            $groupData = [];
            $time = time();
            $groupUserId = [];

            foreach ($data as $index => $item) {
                $id_card = trim($item[5], "\t");
                // 判断以作品名称作为key是否存在
                if (!isset($groupData[$item[3]])) {
                    $groupData[$item[3]] = [];
                }
                // 判断所填写的分组是否存在于
                if (!isset($tagOptions[$item[2]])) {
                    throw new Exception('存在错误的分组信息:' . $item[2]);
                    break;
                }
                if (!isset($studentOptions[$id_card])) {
                    throw new Exception('选手' . $item[1] . '不在该机构的列表内');
                }
                if (!isset($teacherOptions[$item[$teacherIndex]])) {
                    throw new Exception('选手' . $item[1] . '的指导教师手机号不在该机构的列表内');
                }
                // 获取身份证号对应的选手基础信息
                $userItem = $studentOptions[$id_card];

                $groupUserId[$item[3]][] = $userItem['id'];
                $groupData[$item[3]] = [
                    'active_id' => $params['activeId'],
                    'title' => $item[3],
                    'group_tag' => $tagOptions[$item[2]]['id'],
                    'institution_id' => $insOptions[$item[$insIndex]]['id'],
                    'baseinfo_id' => implode('-', $groupUserId[$item[3]]),
                    'wanttime' => $item[$wanttimeIndex],
                    'wanttimeslot' => $item[$wanttimeslotIndex],
                    'remark' => $item[$remarkIndex],
                    'create_time' => $time,
                ];

            }
            // 开启事务
            Db::startTrans();

            $insertGroup = Db::name('active_group_' . $params['activeId'])->insertAll($groupData);

            if ($insertGroup) {
                // 根据作品名称 机构id 创建时间查询刚刚插入的分组数据
                $titleData = array_unique(array_column($data, $titleIndex));
                $condition = [
                    ['title', 'in', $titleData],
                    ['institution_id', 'in', $insIds],
                    ['create_time', '=', $time]
                ];
                $insertFullData = Db::name('active_group_' . $params['activeId'])->where($condition)->select()->toArray();
                $insertFullDataTitles = array_column($insertFullData, 'title');
                // 以作品名称作为刚插入的分组表数据的key
                $groupOptions = convertValueToKey($insertFullData, 'title');
                $userData = [];
                foreach ($data as $index => $item) {
                    $id_card = trim($item[5], "\t");
                    $groupFullItem = $groupOptions[$item[$titleIndex]];
                    $userItem = $studentOptions[$id_card];
                    $teacherItem = $teacherOptions[$item[$teacherIndex]];
                    $insItem = $insOptions[$item[$insIndex]];
                    $userData[] = [
                        'group_id' => $groupFullItem['id'],
                        'institution_name' => $insItem['name'],
                        'institution_id' => $insItem['id'],
                        'issp' => $item[$isspIndex] == '是' ? 1 : 0,
                        'ishz' => $item[$ishzIndex] == '是' ? 1 : 0,
                        'isps' => $item[$ispsIndex] == '是' ? 1 : 0,
                        'create_time' => $time,
                        'postinfo' => $item[15],
                        'baseinfo_id' => $userItem['id'],
                        'guide_teacher' => $teacherItem['id'],
                        'teacher_phone' => $item[$teacherIndex]
                    ];
                }
                $insertUser = Db::name(self::TABLE_NAME . $params['activeId'])->insertAll($userData);
                if (!$insertUser) {
                    throw new Exception('添加报名信息失败');
                } else {
                    Db::commit();
                    return success();
                }
            } else {
                throw new Exception('创建分组失败');
            }
        } catch (\Exception $e) {
            Db::rollback();
            writeLogToDb('导入报名信息失败', $cache['id'], 1, ['message' => $e->getMessage(), 'params' => $request->post('active_id')]);
            return error([], $e->getCode() ?: 9006, $e->getMessage());
        }
    }
    private function splitTimePeriod($start, $end): array
    {
        // 定义时间间隔
        $interval = new DateInterval('P1D'); // P1D 表示 1 天

        // 创建一个日期周期，包含起始日期和结束日期之间的所有日期
        $period = new DatePeriod($start, $interval, $end);
        $timeSegments = [];
        // 遍历日期周期并输出每个日期
        foreach ($period as $date) {
//            echo $date->format('Y-m-d') . PHP_EOL;
            $timeSegments[] = $date->format('Y-m-d');
        }
        return $timeSegments;
    }
}
